﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using MySql.Data.MySqlClient;

namespace MakeIT.MiraPark.Negocios
{
    public static partial class ModelBO
    {

        public static MarcaVO getMarca(int idMarca)
        {
            List<MarcaVO> listaMarcaVo = getMarcas(idMarca);
            if (listaMarcaVo != null && listaMarcaVo.Count > 0)
                return listaMarcaVo[0];
            else
                return null;
        }

        public static List<MarcaVO> getMarcas()
        {
            return getMarcas(null);
        }

        public static List<MarcaVO> getMarcasByTipoVeiculo(int idTipoVeiculo)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT ma.IdMarca, ma.Marca, ma.Ordem ");
            query.AppendLine("FROM marca ma JOIN ");
            query.AppendLine("(modelo mo JOIN tipoveiculo tv on mo.IdTipoVeiculo = tv.IdTipoVeiculo)  on ma.IdMarca=mo.IdMarca ");
            query.AppendLine("WHERE tv.IdTipoVeiculo=?IdTipoVeiculo ");
            query.AppendLine("GROUP BY ma.IdMarca ");
            query.AppendLine("ORDER BY ma.Ordem, ma.Marca ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("IdTipoVeiculo", idTipoVeiculo);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<MarcaVO> listMarca = new List<MarcaVO>();

                while (reader.Read())
                {
                    MarcaVO marcaVO = MarcaVO.CreateMarcaVO();
                    marcaVO.idMarca = reader.GetInt32("IdMarca");
                    marcaVO.marca = reader.GetString("Marca");
                    marcaVO.ordem = reader.GetInt32("Ordem");

                    listMarca.Add(marcaVO);
                }
                reader.Close();
                return listMarca;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }
 
        }
        
        public static List<MarcaVO> getMarcas(int? idMarca)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT IdMarca, Marca, Ordem ");
            query.AppendLine("FROM marca ");
            if (idMarca != null)
            {
                query.AppendLine(where + "IdMarca=?IdMarca ");
                where = " AND ";
            }
            query.AppendLine("ORDER BY Ordem, Marca ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idMarca != null) cmd.Parameters.AddWithValue("IdMarca", idMarca);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<MarcaVO> listMarca = new List<MarcaVO>();

                while (reader.Read())
                {
                    MarcaVO marcaVO = MarcaVO.CreateMarcaVO();
                    marcaVO.idMarca = reader.GetInt32("IdMarca");
                    marcaVO.marca = reader.GetString("Marca");
                    marcaVO.ordem = reader.GetInt32("Ordem");

                    listMarca.Add(marcaVO);
                }
                reader.Close();
                return listMarca;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setMarca(MarcaVO marcaVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE marca ");
            query.AppendLine("SET Marca=?Marca, Ordem=?Ordem ");
            query.AppendLine("WHERE IdMarca=?IdMarca ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdMarca", marcaVO.idMarca);
            cmd.Parameters.AddWithValue("?Marca", marcaVO.marca);
            cmd.Parameters.AddWithValue("?Ordem", marcaVO.ordem);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static long newMarca(MarcaVO marcaVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO marca ");
            query.AppendLine("SET Marca=?Marca, Ordem=?Ordem ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Marca", marcaVO.marca);
            cmd.Parameters.AddWithValue("?Ordem", marcaVO.ordem);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delMarca(MarcaVO marcaVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM marca ");
            query.AppendLine("WHERE IdMarca=?IdMarca ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdMarca", marcaVO.idMarca);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static bool readyToDeleteMarca(int idMarca)
        {
            int contagem = 0;

            MySqlDataReader reader = null;
            StringBuilder query = new StringBuilder();
            MySqlCommand cmd = getConnection();
            cmd.Connection.Open();

            try
            {

                query.AppendLine("SELECT count(*) contagem FROM modelo WHERE IdMarca=?IdMarca ");
                cmd.CommandText = query.ToString();
                cmd.Parameters.AddWithValue("?IdMarca", idMarca);
                reader = cmd.ExecuteReader();
                reader.Read();
                contagem = reader.GetInt32("contagem");
                reader.Close();

                query = new StringBuilder();
                query.AppendLine("SELECT count(*) contagem FROM veiculo WHERE IdMarca=?IdMarca ");
                cmd.CommandText = query.ToString();
                reader = cmd.ExecuteReader();
                reader.Read();
                contagem += reader.GetInt32("contagem");
                reader.Close();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

            return contagem == 0;

        }
    }
}
